package ejb.metrica.matriz.correlacion;

import ig.metrica.riesgo.BandasDTO;
import ig.metrica.riesgo.FactorRiesgoMatrizCorrelacionMetrica;
import ig.metrica.riesgo.ZonaDTO;
import ig.util.ejb.IGBean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Hashtable;
import javax.naming.NamingException;

/**
 * This is the bean class for the MatrizCorrelacionSBBean enterprise bean.
 * Created 9/02/2007 05:10:37 PM
 * @author jsanchez
 */
public class MatrizCorrelacionSBBean implements MatrizCorrelacionSBRemoteBusiness, MatrizCorrelacionSBLocalBusiness {


    protected PreparedStatement pstmt1 = null;
    protected PreparedStatement pstmt2 = null;
    protected PreparedStatement pstmt3 = null;
    protected PreparedStatement pstmt4 = null;
    protected PreparedStatement pstmt5 = null;
    protected PreparedStatement pstmt6 = null;
    private Connection conn = null;
    // <editor-fold defaultstate="collapsed" desc="EJB infrastructure methods. Click the + sign on the left to edit the code.">
    // TODO Add code to acquire and use other enterprise resources (DataSource, JMS, enterprise bean, Web services)
    // TODO Add business methods or web service operations


    /**
     * @see javax.ejb.SessionBean#ejbActivate()
     */
    public void ejbActivate() {
    }

    /**
     * @see javax.ejb.SessionBean#ejbPassivate()
     */
    public void ejbPassivate() {
    }

    /**
     * @see javax.ejb.SessionBean#ejbRemove()
     */
    public void ejbRemove() {
    }
    // </editor-fold>

    /**
     * See section 7.10.3 of the EJB 2.0 specification
     * See section 7.11.3 of the EJB 2.1 specification
     */
    public void ejbCreate() {
        // TODO implement ejbCreate if necessary, acquire resources
        // This method has access to the JNDI context so resource aquisition
        // spanning all methods can be performed here such as home interfaces
        // and data sources.
    }

    public void updateFormScript(Collection<String> cScript) throws Exception {
        try {
            conn = IGBean.getMetricaDW();// getConexion("METRICADW");
            conn.setAutoCommit(false);
            for (String string : cScript) {
                pstmt1 = conn.prepareStatement(string);
                pstmt1.executeUpdate();
                pstmt1.close();
            }
            conn.commit();
        } catch (Exception e) {
            conn.rollback();
            throw e;
        } finally {
            try {
                pstmt1.close();
            } catch (Exception e) {
            }
            /*try {
                conn.close();
            } catch (Exception e) {
            }*/
        }

    }
    // Add business logic below. (Right-click in editor and choose
    // "EJB Methods > Add Business Method" or "Web Service > Add Operation")

    public Integer create(Hashtable CamposAdicionales) throws Exception {

        MatrizDTO matriz = (MatrizDTO) CamposAdicionales.get("DTO");
//        Collection<ZonaDTO> zonas=(Collection) CamposAdicionales.get("ZONAS");
        Collection<ig.metrica.riesgo.AjustesAlternosDTO> ajustes = (Collection) CamposAdicionales.get("AJUSTES_ALTERNOS");
        //TODO implement create
        try {
//            System.out.println("-->ejbCreate");
            conn = IGBean.getMetricaDW();// getConexion("METRICADW");
            pstmt1 = conn.prepareStatement("SELECT MAX(ID) AS MAXIMO FROM MATRICESCORRELACION");
            ResultSet r = pstmt1.executeQuery();
            r.next();
            Integer idasofac = r.getInt("MAXIMO") + 1;
            pstmt1.close();


            pstmt1 = conn.prepareStatement("INSERT INTO MATRICESCORRELACION(ID, NOMBRE, FECHAPUBLICACION, DESCRIPCION, NUMFACTORES, TIPO) VALUES(?, ?, ?, ?, ?, ?)");
            pstmt1.setInt(1, idasofac);
            pstmt1.setString(2, matriz.getNombre());
            pstmt1.setDate(3, (java.sql.Date) matriz.getFechaPublicacion());
            pstmt1.setString(4, matriz.getDescripcion());
            pstmt1.setInt(5, matriz.getNumfactores());
            pstmt1.setInt(6, matriz.getTipo());
            pstmt1.executeUpdate();
            pstmt1.close();
            matriz.setId(idasofac);
            CamposAdicionales.put("DTO", matriz);
        } catch (Exception e) {
            throw new Exception(e.toString());
        } finally {
            try {
                pstmt1.close();
            } catch (Exception e) {
            }
            try {
                pstmt2.close();
            } catch (Exception e) {
            }
            try {
                pstmt3.close();
            } catch (Exception e) {
            }
            /*try {
                conn.close();
            } catch (Exception e) {
            }*/
        }
        try {
            this.update(CamposAdicionales);
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new Exception(ex.toString());
        }
        return matriz.getId();
    }

    public void update(Hashtable CamposAdicionales) throws Exception {
        conn = IGBean.getMetricaDW();// getConexion("METRICADW");
        MatrizDTO matriz = (MatrizDTO) CamposAdicionales.get("DTO");
        int NFactores = 0;
        if (matriz.getTipo().equals(1)) {
            
            Collection<FactorRiesgoMatrizCorrelacionMetrica> frmcms = (Collection) CamposAdicionales.get("FACTORES_EXTRAS");
            pstmt4 = conn.prepareStatement("SELECT MAX(ID) FROM FACTORESRIESGO");
            ResultSet resultSet=pstmt4.executeQuery();
            int ID=1;
            if(resultSet.next()){
                ID+=resultSet.getInt(1);
            }
            try {pstmt4.close();} catch (Exception x) {}
            pstmt4 = conn.prepareStatement("INSERT INTO FACTORESRIESGO(NOMBRE, POSICION, CAMBIOESPERADO, ID, IDMATRIZ) VALUES(?, ?, ?, ?, ?)");
            pstmt5 = conn.prepareStatement("UPDATE FACTORESRIESGO SET NOMBRE= ?, POSICION=?, CAMBIOESPERADO=? WHERE ID = ? AND IDMATRIZ = ?");
            pstmt6 = conn.prepareStatement("DELETE FROM FACTORESRIESGO WHERE ID = ?");
            for (FactorRiesgoMatrizCorrelacionMetrica frmcm : frmcms) {
                if (frmcm.isEliminar()) {  
                    pstmt6.setInt(1, frmcm.getId());
                    pstmt6.executeUpdate();
                    //pstmt6.clearParameters();
                    System.out.println("1-????");
                }else if (frmcm.isNuevo()) {  
                    pstmt4.setString(1, frmcm.getNombre());
                    pstmt4.setInt(2, frmcm.getPosicion());
                    pstmt4.setDouble(3, frmcm.getCambioEsperado());
                    pstmt4.setInt(4, ID++);
                    pstmt4.setInt(5, matriz.getId());
                    pstmt4.executeUpdate();
                    pstmt4.clearParameters();
                    System.out.println("2-????");
                } else {
                    pstmt5.setString(1, frmcm.getNombre());
                    pstmt5.setInt(2, frmcm.getPosicion());
                    pstmt5.setDouble(3, frmcm.getCambioEsperado());
                    pstmt5.setInt(4, frmcm.getId());
                    pstmt5.setInt(5, matriz.getId());
                    pstmt5.executeUpdate();
                    pstmt5.clearParameters();
                    System.out.println("3-????");
                }
            }
            try {pstmt4.close();} catch (Exception x) {}
            try {pstmt5.close();} catch (Exception x) {}
            Collection<ZonaDTO> zonas = (Collection) CamposAdicionales.get("ZONAS");
            Collection<ig.metrica.riesgo.AjustesAlternosDTO> AjustesAlternos = (Collection) CamposAdicionales.get("AJUSTES_ALTERNOS");

            pstmt1 = conn.prepareStatement("UPDATE ZONA SET NUMERO = ?, AJUSTEHORIZONTAL = ? WHERE ID = ? AND IDMATRIZ = ?");
            pstmt2 = conn.prepareStatement("UPDATE BANDAS SET NUMERO=?, AJUSTEVERTICAL=?, LIMITEINFERIOR=?, LIMITESUPERIOR=? WHERE IDZONA = ? AND ID = ?");
            pstmt3 = conn.prepareStatement("UPDATE CAMBIOSESPERADOS_PBS SET CAMBIOESPERADO= ? WHERE IDFACTOR= ? AND IDBANDA= ?");
            for (ZonaDTO zon : zonas) {
                pstmt1.setInt(1, zon.getNumero());
                pstmt1.setDouble(2, zon.getAjusteHorizontal());
                pstmt1.setInt(3, zon.getId());
                pstmt1.setInt(4, matriz.getId());
                pstmt1.executeUpdate();
                pstmt1.clearParameters();


                for (BandasDTO ban : zon.getBandas()) {                
                    pstmt2.setInt(1, ban.getNumero());
                    pstmt2.setDouble(2, ban.getAjusteVertical());
                    pstmt2.setDouble(3, ban.getLimiteInferior());
                    pstmt2.setDouble(4, ban.getLimiteSuperior());
                    pstmt2.setInt(5, ban.getIdZona());
                    pstmt2.setInt(6, ban.getId());
                    pstmt2.executeUpdate();
                    pstmt2.clearParameters();
                    
                    for (FactorRiesgoMatrizCorrelacionMetrica frmcm : ban.getCollection()) {
                        pstmt3.setDouble(1, frmcm.getCambioEsperado());
                        pstmt3.setInt(2, frmcm.getId());
                        pstmt3.setDouble(3, frmcm.getBanda());
                        pstmt3.executeUpdate();
                        pstmt3.clearParameters();
                    }
                }
            }
            try {pstmt1.close();} catch (Exception e) {}
            try {pstmt2.close();} catch (Exception e) {}
            try {pstmt3.close();} catch (Exception e) {}
            pstmt1 = conn.prepareStatement("UPDATE AJUSTESALTERNOS SET AJUSTE = ? WHERE IDZONA1 = ? AND IDZONA2 = ?");
            for (ig.metrica.riesgo.AjustesAlternosDTO ajus : AjustesAlternos) {
                pstmt1.setDouble(1, ajus.getAjuste());
                pstmt1.setInt(2, ajus.getIdZona1() );
                pstmt1.setInt(3, ajus.getIdZona2() );
                pstmt1.executeUpdate();
                pstmt1.clearParameters();
            }
                
        } else if (matriz.getTipo().equals(0)) {
//            System.out.println("Matriz de correlacion");
            Integer posicion = 1;

            pstmt1 = conn.prepareStatement("DELETE FROM VALORESMATRICES WHERE IDMATRIZ = ?");
            pstmt1.setInt(1, matriz.getId());
            pstmt1.execute();
            pstmt1.close();

            pstmt1 = conn.prepareStatement("INSERT INTO VALORESMATRICES (IDMATRIZ, POSICION, VALOR) VALUES(?, ?, ?)");
            Object[][] matrizcorrelacion = (Object[][]) CamposAdicionales.get("MATRIZ");
            for (int i = 0; i < matrizcorrelacion.length; i++) {
                for (int j = 0; j < matrizcorrelacion.length; j++) {
                    pstmt1.setInt(1, matriz.getId());
                    pstmt1.setInt(2, posicion);
                    pstmt1.setDouble(3, Double.valueOf(matrizcorrelacion[i][j].toString()));
                    pstmt1.execute();
                    pstmt1.clearParameters();
                    posicion++;
                }
            }
            pstmt1.close();
            pstmt1 = conn.prepareStatement("SELECT MAX(ID) AS MAXIMO FROM FACTORESRIESGO");
            ResultSet r = pstmt1.executeQuery();
            r.next();
            Integer idasofac = r.getInt("MAXIMO") + 1;
            pstmt1.close();

            Collection<FactorRiesgoMatrizCorrelacionMetrica> factoresRiesgo = (Collection<FactorRiesgoMatrizCorrelacionMetrica>) CamposAdicionales.get("FACTORES");
            for (FactorRiesgoMatrizCorrelacionMetrica elem : factoresRiesgo) {
                if (elem.isNuevo() == null) {
                    pstmt1 = conn.prepareStatement("DELETE FROM FACTORESRIESGOPERSONALES WHERE IDFACTORMATRIZ = ? ");
                    pstmt1.setInt(1, elem.getId());
                    pstmt1.execute();
                    pstmt1.close();

                    pstmt1 = conn.prepareStatement("DELETE FROM FACTORESRIESGO WHERE ID = ? AND IDMATRIZ = ?");
                    pstmt1.setInt(1, elem.getId());
                    pstmt1.setInt(2, matriz.getId());
                    pstmt1.execute();
                    pstmt1.close();
                } else if (elem.isNuevo()) {
                    pstmt1 = conn.prepareStatement("INSERT INTO FACTORESRIESGO(NOMBRE, POSICION, CAMBIOESPERADO, ID, IDMATRIZ) VALUES(?, ?, ?, ?, ?)");
                    elem.setId(idasofac++);
                    elem.setNuevo(false);
                } else if (!elem.isNuevo()) {
                    pstmt1 = conn.prepareStatement("UPDATE FACTORESRIESGO SET NOMBRE= ?, POSICION=?, CAMBIOESPERADO=? WHERE ID = ? AND IDMATRIZ = ?");
                }
                if (elem.isNuevo() != null) {
                    pstmt1.setString(1, elem.getNombre());
                    pstmt1.setInt(2, elem.getPosicion());
                    pstmt1.setDouble(3, elem.getCambioEsperado());
                    pstmt1.setInt(4, elem.getId());
                    pstmt1.setInt(5, matriz.getId());
                    pstmt1.executeUpdate();
                    pstmt1.close();
                    NFactores++;
                    if (matriz.getTipo().equals(0)) {
                        if (!elem.isNuevo()) {
                            pstmt2 = conn.prepareStatement("DELETE FROM CAMBIOSESPERADOS_PBS WHERE IDFACTOR = ?");
                            pstmt2.setInt(1, elem.getId());
                            pstmt2.executeUpdate();
                            pstmt2.close();
                        }
                    }
                }
            }

        }
        matriz.setNumfactores(NFactores);
        pstmt1 = conn.prepareStatement("UPDATE MATRICESCORRELACION SET NOMBRE=?, DESCRIPCION=?, NUMFACTORES=?,  FECHAPUBLICACION=?, TIPO=?  WHERE ID=?");
        pstmt1.setString(1, matriz.getNombre());
        pstmt1.setString(2, matriz.getDescripcion());
        pstmt1.setInt(3, matriz.getNumfactores());
        pstmt1.setDate(4, (java.sql.Date) matriz.getFechaPublicacion());
        pstmt1.setInt(5, matriz.getTipo());
        pstmt1.setInt(6, matriz.getId());
        pstmt1.executeUpdate();
        pstmt1.close();

        if (pstmt1 != null) {
            pstmt1.close();
        }
        /*if (!conn.isClosed()) {
            conn.close();
        }*/
    }

    @SuppressWarnings("empty-statement")
    public void remove(Integer id) throws Exception {
        conn = IGBean.getMetricaDW();// getConexion("METRICADW");
        pstmt1 = conn.prepareStatement("SELECT ID FROM FACTORESRIESGO WHERE IDMATRIZ=?");
        pstmt1.setInt(1, id);
        ResultSet resultado = pstmt1.executeQuery();

        PreparedStatement pstmtD = conn.prepareStatement("DELETE FROM CAMBIOSESPERADOS_PBS WHERE IDFACTOR = ?");
        pstmt1 = conn.prepareStatement("DELETE FROM FACTORESRIESGOPERSONALES WHERE IDFACTORMATRIZ = ? ");

        while (resultado.next()) {
            pstmtD.setInt(1, resultado.getInt("ID"));
            pstmtD.executeUpdate();
            pstmtD.clearParameters();

            pstmt1.setInt(1, resultado.getInt("ID"));
            pstmt1.execute();
            pstmt1.clearParameters();
        }

        pstmtD.close();
        pstmt1.close();

        try {
            resultado.close();
        } catch (Exception e) {
        }
        ;

        pstmt1 = conn.prepareStatement("DELETE FROM FACTORESRIESGO WHERE IDMATRIZ=?");
        pstmt1.setInt(1, id);
        pstmt1.executeUpdate();

        pstmt1.close();
        pstmt1 = conn.prepareStatement("DELETE FROM VALORESMATRICES WHERE IDMATRIZ=?");
        pstmt1.setInt(1, id);
        pstmt1.executeUpdate();

        pstmt1.close();
        pstmt1 = conn.prepareStatement("SELECT ID FROM ZONA WHERE IDMATRIZ=?");
        pstmt1.setInt(1, id);
        resultado = pstmt1.executeQuery();

        pstmtD = conn.prepareStatement("DELETE FROM BANDAS WHERE IDZONA = ?");
        PreparedStatement pstmtD1 = conn.prepareStatement("DELETE FROM AJUSTESALTERNOS WHERE IDZONA1 = ?  OR IDZONA2 = ?");
        while (resultado.next()) {
            pstmtD.setInt(1, resultado.getInt("ID"));
            pstmtD.executeUpdate();
            pstmtD.clearParameters();

            pstmtD1.setInt(1, resultado.getInt("ID"));
            pstmtD1.setInt(2, resultado.getInt("ID"));
            pstmtD1.executeUpdate();
            pstmtD1.clearParameters();
        }
        pstmtD.close();
        pstmt1.close();
        try {
            resultado.close();
        } catch (Exception e) {
        }
        ;
        /*******/
        pstmt1.close();
        pstmt1 = conn.prepareStatement("DELETE FROM ZONA WHERE IDMATRIZ = ?");
        pstmt1.setInt(1, id);
        pstmt1.executeUpdate();


        //aqui para matriz de correlacion

        pstmt1.close();
        pstmt1 = conn.prepareStatement("DELETE FROM MATRICESCORRELACION WHERE ID=?");
        pstmt1.setInt(1, id);
        pstmt1.executeUpdate();
        //conn.close();
    }

    @SuppressWarnings("empty-statement")
    public Hashtable findById(Integer id) throws Exception {

        Hashtable CamposAdicionales = new Hashtable();
        MatrizDTO matriz = new MatrizDTO();
        Collection<FactorRiesgoMatrizCorrelacionMetrica> factoresRiesgoFull = new ArrayList<FactorRiesgoMatrizCorrelacionMetrica>();
        Collection<ZonaDTO> zonas = new ArrayList<ZonaDTO>();
        Collection<ig.metrica.riesgo.AjustesAlternosDTO> AjustesAlternos = new ArrayList<ig.metrica.riesgo.AjustesAlternosDTO>();
        try {
            conn = IGBean.getMetricaDW();// getConexion("METRICADW");
            pstmt1 = conn.prepareStatement(
                    "SELECT ID,NOMBRE, FECHAPUBLICACION, NUMFACTORES, DESCRIPCION, TIPO " +
                    "FROM MATRICESCORRELACION " +
                    "WHERE ID=?");
            pstmt1.setInt(1, id);
            ResultSet r = pstmt1.executeQuery();
            if (r.next()) {

                matriz.setNombre(r.getString("NOMBRE"));
                matriz.setFechaPublicacion(r.getDate("FECHAPUBLICACION"));
                matriz.setNumfactores(r.getInt("NUMFACTORES"));
                matriz.setDescripcion(r.getString("DESCRIPCION"));
                matriz.setId(r.getInt("ID"));
                matriz.setTipo(r.getInt("TIPO"));
                pstmt1.close();
                CamposAdicionales.put("DTO", matriz);

                if (matriz.getTipo().equals(1)) {
                    pstmt1 = conn.prepareStatement("SELECT ID, IDMATRIZ, NUMERO, AJUSTEHORIZONTAL FROM ZONA WHERE IDMATRIZ = ? ORDER BY NUMERO");
                    pstmt2 = conn.prepareStatement("SELECT ID, IDZONA, NUMERO, AJUSTEVERTICAL,LIMITEINFERIOR,LIMITESUPERIOR FROM BANDAS WHERE IDZONA = ? ORDER BY NUMERO");
                    pstmt3 = conn.prepareStatement("SELECT IDZONA1, IDZONA2, AJUSTE FROM AJUSTESALTERNOS WHERE IDZONA1=? OR IDZONA2 = ?");
                    pstmt4 = conn.prepareStatement(
                            "SELECT FAC.ID, CE.CAMBIOESPERADO, FAC.POSICION, FAC.NOMBRE, CE.IDBANDA" +
                            " FROM FACTORESRIESGO FAC" +
                            " INNER JOIN CAMBIOSESPERADOS_PBS CE ON CE.IDFACTOR=FAC.ID AND FAC.IDMATRIZ = ? AND CE.IDBANDA = ?");
                    pstmt5 = conn.prepareStatement(
                            " SELECT FAC.ID, FAC.POSICION, FAC.NOMBRE,  FAC.CAMBIOESPERADO" +
                            " FROM FACTORESRIESGO FAC" +
                            " WHERE FAC.IDMATRIZ = ?");

                    pstmt1.setInt(1, matriz.getId());
                    r = pstmt1.executeQuery();
                    AjustesAlternos = new ArrayList<ig.metrica.riesgo.AjustesAlternosDTO>();

                    while (r.next()) {
                        Collection<BandasDTO> Bandas = new ArrayList<BandasDTO>();
                        ZonaDTO ZonaTMP = new ZonaDTO(r.getInt("ID"), r.getInt("IDMATRIZ"), r.getInt("NUMERO"), r.getDouble("AJUSTEHORIZONTAL"), matriz.getId() == 2 ? "Zona" : "Matriz Crhoque");
                        //cargar las bandas
                        pstmt2.setInt(1, r.getInt("ID"));
                        ResultSet r1 = pstmt2.executeQuery();
                        while (r1.next()) {
                            BandasDTO bandaTMP = new BandasDTO(r1.getInt("IDZONA"), r1.getDouble("AJUSTEVERTICAL"), r1.getInt("ID"), r1.getInt("NUMERO"), matriz.getId() == 2 ? "Banda" : "Nodo");
                            bandaTMP.setLimiteInferior(r1.getDouble("LIMITEINFERIOR"));
                            bandaTMP.setLimiteSuperior(r1.getDouble("LIMITESUPERIOR"));

                            //cargar los facotes
                            pstmt4.setInt(1, matriz.getId());
                            pstmt4.setInt(2, r1.getInt("ID"));
                            ResultSet r3 = pstmt4.executeQuery();
                            Collection factoresRiesgo = new ArrayList();
                            while (r3.next()) {
                                FactorRiesgoMatrizCorrelacionMetrica factor = new FactorRiesgoMatrizCorrelacionMetrica(r3.getString("NOMBRE"), r3.getDouble("CAMBIOESPERADO"), r3.getInt("POSICION"), r3.getInt("ID"));
                                factor.cambioEsperadoPBS.setIdBanda(r3.getInt("IDBANDA"));
                                factor.cambioEsperadoPBS.setCambioEsperado(r3.getDouble("CAMBIOESPERADO"));
                                factoresRiesgo.add(factor);
                                boolean add = true;
                                for (Object elem : factoresRiesgoFull) {
                                    if (((FactorRiesgoMatrizCorrelacionMetrica) elem).getNombre().trim().equals(factor.getNombre().trim())) {
                                        add = false;
                                    }
                                }
                                if (add) {
                                    factoresRiesgoFull.add(factor);
                                }
                            }
                            r3 = null;
                            pstmt4.clearParameters();
                            bandaTMP.setFactores(factoresRiesgo);
                            Bandas.add(bandaTMP);
                        }
                        pstmt2.clearParameters();
                        ZonaTMP.setBandas(Bandas);
                        //caraga los ajustes alternos
                        pstmt3.setInt(1, r.getInt("ID"));
                        pstmt3.setInt(2, r.getInt("ID"));
                        r1 = pstmt3.executeQuery();
                        while (r1.next()) {
                            boolean adicionar = true;
                            if (AjustesAlternos.size() == 0) {
                                AjustesAlternos.add(new ig.metrica.riesgo.AjustesAlternosDTO(r1.getInt("IDZONA1"), r1.getInt("IDZONA2"), r1.getDouble("AJUSTE")));
                            } else {
                                for (ig.metrica.riesgo.AjustesAlternosDTO elem : AjustesAlternos) {
                                    if ((elem.getIdZona1().equals(r1.getInt("IDZONA1")) && elem.getIdZona2().equals(r1.getInt("IDZONA2"))) ||
                                            (elem.getIdZona2().equals(r1.getInt("IDZONA1")) && elem.getIdZona1().equals(r1.getInt("IDZONA2")))) {
                                        adicionar = false;
                                    }
                                }
                                if (adicionar) {
                                    AjustesAlternos.add(new ig.metrica.riesgo.AjustesAlternosDTO(r1.getInt("IDZONA1"), r1.getInt("IDZONA2"), r1.getDouble("AJUSTE")));
                                }
                            }
                        }
                        pstmt3.clearParameters();
                        zonas.add(ZonaTMP);
                    }

                    Collection<FactorRiesgoMatrizCorrelacionMetrica> factoresRiesgo = new ArrayList<FactorRiesgoMatrizCorrelacionMetrica>();
                    pstmt5.setInt(1, matriz.getId());
                    ResultSet r3 = pstmt5.executeQuery();
                    while (r3.next()) {
                        FactorRiesgoMatrizCorrelacionMetrica factor = new FactorRiesgoMatrizCorrelacionMetrica(r3.getString("NOMBRE"), r3.getDouble("CAMBIOESPERADO"), r3.getInt("POSICION"), r3.getInt("ID"));
                        factor.getCambioEsperadoPBS().setCambioEsperado(r3.getDouble("CAMBIOESPERADO"));
                        boolean add = true;
                        for (FactorRiesgoMatrizCorrelacionMetrica elem : factoresRiesgoFull) {
                            if (elem.getNombre().trim().equals(factor.getNombre().trim())) {
                                add = false;
                            }
                        }
                        if (add) {
                            factoresRiesgo.add(factor);
                            factoresRiesgoFull.add(factor);
                        }
                    }

                    CamposAdicionales.put("ZONAS", zonas);
                    CamposAdicionales.put("AJUSTES_ALTERNOS", AjustesAlternos);
                    CamposAdicionales.put("FACTORES_EXTRAS", factoresRiesgo);
                    CamposAdicionales.put("FACTORES", factoresRiesgoFull);
                } else if (matriz.getTipo().equals(0)) {

//                    System.out.println("Matriz de correlacion");
                    pstmt1 = conn.prepareStatement(
                            "SELECT NOMBRE, CAMBIOESPERADO, POSICION, ID " +
                            "FROM FACTORESRIESGO " +
                            "WHERE IDMATRIZ=? " +
                            "ORDER BY POSICION ");
                    pstmt1.setInt(1, matriz.getId());
                    r = pstmt1.executeQuery();

                    Collection factoresRiesgo = new ArrayList();
                    while (r.next()) {
                        FactorRiesgoMatrizCorrelacionMetrica factor = new FactorRiesgoMatrizCorrelacionMetrica(r.getString("NOMBRE"), r.getDouble("CAMBIOESPERADO"), r.getInt("POSICION"), r.getInt("ID"));
                        factoresRiesgo.add(factor);
                    }
                    pstmt1.close();
                    pstmt1 = conn.prepareStatement("SELECT COUNT(*) FROM VALORESMATRICES WHERE IDMATRIZ=?");
                    pstmt1.setInt(1, matriz.getId());
                    r = pstmt1.executeQuery();
                    r.next();
                    int nFactores = Integer.valueOf(r.getObject(1).toString());
                    pstmt1.close();
                    int N = 0;
                    while (true) {
                        if ((N * N) == nFactores) {
                            break;
                        } else if ((N * N) > nFactores) {
                            throw new Exception("La matriz de correlación, tiene un numero de factores incorrecto\no los valores no coresponden con lo factores");
                        } else {
                            N++;
                        }
                    }
                    pstmt1 = conn.prepareStatement(
                            "SELECT VALOR, POSICION FROM VALORESMATRICES " +
                            "WHERE IDMATRIZ=? ORDER BY POSICION");
                    pstmt1.setInt(1, matriz.getId());
                    r = pstmt1.executeQuery();
                    Double[][] matrizcorrelacion = new Double[N][N];
                    while (r.next()) {
                        double valor = r.getDouble("VALOR");
                        int posicion = r.getInt("POSICION") - 1;
//                    if((posicion/N)==(posicion % N))
                        matrizcorrelacion[posicion / N][posicion % N] = valor;
                    }
                    CamposAdicionales.put("FACTORES", factoresRiesgo);
                    CamposAdicionales.put("MATRIZ", matrizcorrelacion);
                }
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt2.close();
            } catch (Exception e) {
            }
            try {
                pstmt1.close();
            } catch (Exception e) {
            }
            try {
                pstmt3.close();
            } catch (Exception e) {
            }
            try {
                pstmt4.close();
            } catch (Exception e) {
            }
            try {
                pstmt5.close();
            } catch (Exception e) {
            }
            try {
                pstmt1.close();
            } catch (Exception e) {
            }
            /*try {
                conn.close();
            } catch (Exception e) {
            }*/
        }
        return CamposAdicionales;
    }


    public Collection<MatrizDTO> findAll() throws Exception {
        Collection<MatrizDTO> matrices = new ArrayList<MatrizDTO>();
        conn = IGBean.getMetricaDW();//getConexion("METRICADW");
        pstmt1 = conn.prepareStatement("SELECT ID,NOMBRE, FECHAPUBLICACION, NUMFACTORES, DESCRIPCION, TIPO " +
                "FROM MATRICESCORRELACION ");
        ResultSet r = pstmt1.executeQuery();
        while (r.next()) {
            MatrizDTO matriz = new MatrizDTO();
            matriz.setNombre(r.getString("NOMBRE"));
            matriz.setFechaPublicacion(r.getDate("FECHAPUBLICACION"));
            matriz.setNumfactores(r.getInt("NUMFACTORES"));
            matriz.setDescripcion(r.getString("DESCRIPCION"));
            matriz.setId(r.getInt("ID"));
            matriz.setTipo(r.getInt("TIPO"));
            matrices.add(matriz);
        }
        pstmt1.close();
        //conn.close();
        return matrices;
    }
}
